"""
模拟浏览器行为，获取东方财富的股票的历史主力资金信息

补起历史某一天的历史数据，而且是空的才补

"""

# pip install selenium

from selenium import webdriver
import time, datetime
from bs4 import BeautifulSoup
import pymysql
import random
import pandas as pd

browser = webdriver.Chrome('chromedriver3.exe')
url_history = 'http://data.eastmoney.com/zjlx/{}.html'
# 获取所有的代码
conn3 = pymysql.connect(host='rm-2vc83ot65e551h73beo.mysql.cn-chengdu.rds.aliyuncs.com', port=3306, user='river',
                        passwd='lihua201314SJ', db='stock', charset="utf8")
conn = conn3
cursor = conn.cursor()


# 补某一天的
day_no = '2022-01-11'

sql = f"""
SELECT DISTINCT `code`,name FROM stock.`stock_history_money_d` WHERE day_no='2022-01-10'
and `code` NOT IN (SELECT DISTINCT `code` FROM stock.`stock_history_money_d` WHERE day_no='{day_no}')
"""

code_list = pd.read_sql(sql, conn).to_dict('records')

print(f"需要补数的有{len(code_list)}只")


def change_unit(num):
    if num[-1] == '亿':
        return float(num[:-1]) * 100000000
    elif num[-1] == '万':
        return float(num[:-1]) * 10000
    elif num == '' or num == ' ' or num == '-':
        return 0
    else:
        return int(num[:-3])


for item in code_list:
    code = item['code']
    name = item['name']
    # 进入网站
    browser.get(url_history.format(code))
    time.sleep(0.5)
    try:
        # 使用beautifulSoup解析网页标签
        html = BeautifulSoup(browser.page_source, "lxml")
        money_info_today = html.select(".dataview tbody tr")
        for jtem in money_info_today:
            jtem_list = jtem.find_all('td')
            jtem_list_clean = [str(i.text).strip().replace(' ', '') for i in jtem_list]
            code = code
            name = name
            if day_no == jtem_list_clean[0]:
                price = 0 if jtem_list_clean[1] == '-' else jtem_list_clean[1]
                change_rate = 0 if jtem_list_clean[2] == '-' else jtem_list_clean[2][:-1]
                main_money_num = change_unit(jtem_list_clean[3])
                main_money_rate = 0 if jtem_list_clean[4] == '-' else jtem_list_clean[4][:-1]
                super_big_money_num = change_unit(jtem_list_clean[5])
                super_big_money_rate = 0 if jtem_list_clean[6] == '-' else jtem_list_clean[6][:-1]
                big_money_num = change_unit(jtem_list_clean[7])
                big_money_rate = 0 if jtem_list_clean[8] == '-' else jtem_list_clean[8][:-1]
                medium_money_num = change_unit(jtem_list_clean[9])
                medium_money_rate = 0 if jtem_list_clean[10] == '-' else jtem_list_clean[10][:-1]
                small_money_num = change_unit(jtem_list_clean[11])
                small_money_rate = 0 if jtem_list_clean[12] == '-' else jtem_list_clean[12][:-1]
                # 插入
                sql = f"""
                    insert into stock.stock_history_money_d
                          (code,name,day_no,price,change_rate,main_money_num,super_big_money_num,big_money_num,medium_money_num,small_money_num,main_money_rate,super_big_money_rate,big_money_rate,medium_money_rate,small_money_rate,load_time)
                    values('{code}','{name}','{day_no}',{price},{change_rate},{main_money_num},{super_big_money_num},{big_money_num},{medium_money_num},{small_money_num},{main_money_rate},{super_big_money_rate},{big_money_rate},{medium_money_rate},{small_money_rate},current_timestamp)
                    """
                cursor.execute(sql)
                conn.commit()
                print(code, '\t', name)
                break
    except Exception as e:
        print(e)
        print(code, '\t', name)
        print(sql)
cursor.close()
conn.close()
